package com.shop.prj.dao.Impl;

import com.shop.prj.bean.Book;
import com.shop.prj.bean.Manager;
import com.shop.prj.bean.User;
import com.shop.prj.dao.UserDao;
import com.shop.prj.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
	
	 public boolean login(User user) {
	        String sql = "SELECT * FROM `user` WHERE `user_login_name` = '" + user.getUserLoginName() + "' AND `user_password` = '"
	                + user.getUserPassword() + "'";
	     
	        Connection connection = JDBCUtils.getConnection();
	       
	        Statement statement = null;
	       
	        ResultSet resultSet = null;
	        User user2 = null;
	        try {
	         
	            statement = connection.createStatement();
	            
	            resultSet = statement.executeQuery(sql);
	       
	            if (resultSet.next()) {
	              user2 = new User();
	                user2.setUserId(resultSet.getInt(1));
	                user2.setUserLoginName(resultSet.getString(2));
	                user2.setUserPassword(resultSet.getString(3));
	                user2.setUserName(resultSet.getString(4));
	                user2.setUserSex(resultSet.getString(5));
	                user2.setUserEmail(resultSet.getString(6));
	                user2.setUserTel(resultSet.getString(7));
	                user2.setUserLevel(resultSet.getInt(8));
	                user2.setUserAddress(resultSet.getString(9));
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                resultSet.close();
	                statement.close();
	                connection.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return (user2 != null);
	    }
    @Override
    public int insert(User user) {
        String sql="INSERT INTO `user`(`user_login_name`,`user_password`,`user_name`,`user_sex`,`user_email`,`user_tel`,`user_level`,`user_address`)  VALUES (?,?,?,?,?,?,?,?)";
        int result=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection= JDBCUtils.getConnection();
        try {
            preparedStatement=connection.prepareStatement(sql);
          preparedStatement.setString(1, user.getUserLoginName());
            preparedStatement.setString(2,user.getUserPassword());
            preparedStatement.setString(3, user.getUserName());
            preparedStatement.setString(4, user.getUserSex());
            preparedStatement.setString(5, user.getUserEmail());
            preparedStatement.setString(6, user.getUserTel());
            preparedStatement.setInt(7, user.getUserLevel());
            preparedStatement.setString(8,user.getUserAddress());

            result=preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();

            }
        }
        return result;
    }

    @Override
    public int update(User user) {
        String sql="UPDATE `user` SET `user_login_name`=?,`user_password`=?,`user_name`=?,`user_sex`=?, `user_email`=?,`user_tel`=?,`user_level`=?,`user_address`=? WHERE  `user_id`=?";
        int result=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection= JDBCUtils.getConnection();
        try {
        	
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getUserLoginName());
            preparedStatement.setString(2,user.getUserPassword());
            preparedStatement.setString(3, user.getUserName());
            preparedStatement.setString(4, user.getUserSex());
            preparedStatement.setString(5, user.getUserEmail());
            preparedStatement.setString(6, user.getUserTel());
            preparedStatement.setInt(7, user.getUserLevel());
            preparedStatement.setString(8,user.getUserAddress());
            preparedStatement.setInt(9, user.getUserId());

            result=preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();

            }
        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM `user` WHERE `user_id` = ?";
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        int result = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public User queryUserId(Long id) {
        String sql = "SELECT * FROM `user` WHERE `user_id`=?";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;
        User user = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setUserId(resultSet.getInt(1));
                user.setUserLoginName(resultSet.getString(2));
                user.setUserPassword(resultSet.getString(3));
                user.setUserName(resultSet.getString(4));
                user.setUserSex(resultSet.getString(5));
                user.setUserEmail(resultSet.getString(6));
                user.setUserTel(resultSet.getString(7));
                user.setUserLevel(resultSet.getInt(8));
                user.setUserAddress(resultSet.getString(9));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return user;
    }

    @Override
    public List<User> queryAll() {
        String sql = "SELECT * FROM `user`";

        Connection connection = JDBCUtils.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<User>();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {

                User user = new User();
                user.setUserId(resultSet.getInt(1));
                user.setUserLoginName(resultSet.getString(2));
                user.setUserPassword(resultSet.getString(3));
                user.setUserName(resultSet.getString(4));
                user.setUserSex(resultSet.getString(5));
                user.setUserEmail(resultSet.getString(6));
                user.setUserTel(resultSet.getString(7));
                user.setUserLevel(resultSet.getInt(8));
                user.setUserAddress(resultSet.getString(9));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<User> queryByUserName(String name) {
        String sql = "SELECT * FROM `user`WHERE `user_login_name`LIKE ?";


        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<User>();
        User user=null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "%" + name + "%");

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                user = new User();
                user.setUserId(resultSet.getInt(1));
                user.setUserLoginName(resultSet.getString(2));
                user.setUserPassword(resultSet.getString(3));
                user.setUserName(resultSet.getString(4));
                user.setUserSex(resultSet.getString(5));
                user.setUserEmail(resultSet.getString(6));
                user.setUserTel(resultSet.getString(7));
                user.setUserLevel(resultSet.getInt(8));
                user.setUserAddress(resultSet.getString(9));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
	@Override
	public User queryUser(String name) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `user` WHERE `user_login_name`=?";

        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;
        User user = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setUserId(resultSet.getInt(1));
                user.setUserLoginName(resultSet.getString(2));
                user.setUserPassword(resultSet.getString(3));
                user.setUserName(resultSet.getString(4));
                user.setUserSex(resultSet.getString(5));
                user.setUserEmail(resultSet.getString(6));
                user.setUserTel(resultSet.getString(7));
                user.setUserLevel(resultSet.getInt(8));
                user.setUserAddress(resultSet.getString(9));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return user;
	}
}
